using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for BookDAO
/// </summary>
public class BookDAO:DBAccess
{
    #region sql command
    string SQL_GET_RECOMMENDED = "select * from dbo.items where is_recommended=1";
    string SQL_GET_BY_CATEGORY = "select * from dbo.items where category_id=@cateid";
    string SQL_GET_BOOK = "select item_id, a.category_id, a.name, author, price, image_url, notes,is_recommended, rating, rating_count,b.name from dbo.items as a join dbo.categories as b on a.category_id=b.category_id where item_id=@item_id";
    string SQL_INSERT = "insert into dbo.items(category_id,[name],[author],price,image_url,notes, is_recommended) values(@category_id,@name,@author,@price,@image_url,@notes, @is_recommended)";
    string SQL_UPDATE = "update dbo.items set category_id=@category_id,[name]=@name,[author]=@author,price=@price,image_url=@image_url,notes=@notes, is_recommended=@is_recommended where item_id=@item_id";
    string SQL_DELETE = "delete from dbo.items where item_id=@item_id";
    #endregion
    #region fields
    string message = "";
    #endregion
    #region properties
    public string Message
    {
        get { return message; }
    }
    #endregion
    public BookDAO()
	{
		//
		// TODO: Add constructor logic here
		//
    }
    #region methods
    public IList<Book> GetRecommendedItems(){
 
        IList<Book> list=new List<Book>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_GET_RECOMMENDED, conn);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Book book = new Book();
                    book.BookID = dr.GetInt32(0);
                    book.Category.CategoryID = dr.GetInt32(1);
                    book.Name = dr.GetString(2);
                    book.Author = dr.GetString(3);
                    book.Price = dr.GetDecimal(4);
                    book.ImageURL = dr.GetString(5);
                    book.Note = dr.GetString(6);
                    book.IsRecommended = dr.GetByte(7);
                    book.Rating = dr.GetInt32(8);
                    book.RatingCount = dr.GetInt32(9);
                    list.Add(book);

                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    public IList<Book> GetBookByCategory(int categoryID)
    {

        IList<Book> list = new List<Book>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_GET_BY_CATEGORY, conn);
            command.Parameters.AddWithValue("@cateid",categoryID);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Book book = new Book();
                    book.BookID = dr.GetInt32(0);
                    book.Category.CategoryID = dr.GetInt32(1);
                    book.Name = dr.GetString(2);
                    book.Author = dr.GetString(3);
                    book.Price = dr.GetDecimal(4);
                    book.ImageURL = dr.GetString(5);
                    book.Note = dr.GetString(6);
                    book.IsRecommended = dr.GetByte(7);
                    book.Rating = dr.GetInt32(8);
                    book.RatingCount = dr.GetInt32(9);
                    list.Add(book);

                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    public IList<Book> GetBookByID(int bookId)
    {

        IList<Book> list = new List<Book>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_GET_BOOK, conn);
            command.Parameters.AddWithValue("@item_id", bookId);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Book book = new Book();
                    book.BookID = dr.GetInt32(0);
                    book.Category.CategoryID = dr.GetInt32(1);
                    book.Name = dr.GetString(2);
                    book.Author = dr.GetString(3);
                    book.Price = dr.GetDecimal(4);
                    book.ImageURL = dr.GetString(5);
                    book.Note = dr.GetString(6);
                    book.IsRecommended = dr.GetByte(7);
                    book.Rating = dr.GetInt32(8);
                    book.RatingCount = dr.GetInt32(9);
                    book.Category.CategoryName = dr.GetString(10);

                    list.Add(book);

                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    public int Insert(Book book)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_INSERT, conn);
            command.Parameters.AddWithValue("@category_id", book.Category.CategoryID);
            command.Parameters.AddWithValue("@name", book.Name);
            command.Parameters.AddWithValue("@author", book.Author);
            command.Parameters.AddWithValue("@price", book.Price);
            command.Parameters.AddWithValue("@image_url", book.ImageURL);
            command.Parameters.AddWithValue("@notes", book.Note);
            command.Parameters.AddWithValue("@is_recommended", book.IsRecommended);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                command.CommandText = "select @@IDENTITY from dbo.items";
                book.BookID = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    public int Update(Book book)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
            command.Parameters.AddWithValue("@category_id", book.Category.CategoryID);
            command.Parameters.AddWithValue("@name", book.Name);
            command.Parameters.AddWithValue("@author", book.Author);
            command.Parameters.AddWithValue("@price", book.Price);
            command.Parameters.AddWithValue("@image_url", book.ImageURL);
            command.Parameters.AddWithValue("@notes", book.Note);
            command.Parameters.AddWithValue("@is_recommended", book.IsRecommended);
            command.Parameters.AddWithValue("@item_id", book.BookID);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();                
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    public int Delete(Book book)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_DELETE, conn);         
            command.Parameters.AddWithValue("@item_id", book.BookID);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();                
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }
    #endregion
}
